﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using log4net;


namespace QLDBHYT
{
    class dbConnection
    {
        private SqlDataAdapter myAdapter;
        public SqlConnection conn;
        public  SqlTransaction transaction;
        private static readonly ILog logger = LogManager.GetLogger(
          System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        /// <constructor>
        /// Initialise Connection
        /// </constructor>
        public dbConnection()
        {
            myAdapter = new SqlDataAdapter();
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString);
            
        }
        
        /// <method>
        /// Open Database Connection if Closed or Broken
        /// </method>
        public SqlConnection openConnection()
        {
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {

                conn.Open();
            }
            return conn;
        }

        /// <method>
        /// Select Query
        /// </method>
        public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            dataTable = null;
            DataSet ds = new DataSet();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(ds);
                dataTable = ds.Tables[0];
            }
            catch (SqlException e)
            {
                //Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
                logger.Error("Error - Connection.executeSelectQuery - Query: " + _query);
                logger.Error("Value " + Ketqua(sqlParameter));
                logger.Error(e);
                return null;
            }
            finally
            {

            }
            return dataTable;
        }
        public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter,SqlTransaction tran)
        {
            SqlCommand myCommand = new SqlCommand();
            myCommand.Transaction = tran;
            DataTable dataTable = new DataTable();
            dataTable = null;
            DataSet ds = new DataSet();
            try
            {

                myCommand.Connection = conn;
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(ds);
                dataTable = ds.Tables[0];
            }
            catch (SqlException e)
            {
                //Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
                logger.Error("Error - Connection.executeSelectQuery - Query: " + _query);
                logger.Error("Value " + Ketqua(sqlParameter));
                logger.Error(e);
                return null;
            }
            finally
            {

            }
            return dataTable;
        }
        /// <method>
        /// Insert Query
        /// </method>
        public bool executeInsertQuery(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myAdapter.InsertCommand = myCommand;
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                logger.Error("Error - Connection.executeSelectQuery - Query: " + _query);
                logger.Error("Value " + Ketqua(sqlParameter));
                logger.Error(e);
                return false;
            }
            finally
            {
                
            }
            return true;
        }

        /// <method>
        /// Update Query
        /// </method>
        public bool executeUpdateQuery(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myAdapter.UpdateCommand = myCommand;
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                logger.Error("Error - Connection.executeSelectQuery - Query: " + _query);
                logger.Error("Value " + Ketqua(sqlParameter));
                logger.Error(e);
                return false;
            }
            finally
            {
            }
            return true;
        }
        public bool executeUpdateQuery(String _query, SqlParameter[] sqlParameter,SqlTransaction tran)
        {
            SqlCommand myCommand = new SqlCommand();
            myCommand.Transaction = tran;
            try
            {
                myCommand.Connection = conn;
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myAdapter.UpdateCommand = myCommand;
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                logger.Error("Error - Connection.executeSelectQuery - Query: " + _query);
                logger.Error("Value " + Ketqua(sqlParameter));
                logger.Error(e);
                return false;
            }
            finally
            {
            }
            return true;
        }
        public string Ketqua(SqlParameter[] sqlParameter)
        {
            string  value="";
            for (int i = 0; i < sqlParameter.Length; i++)
            {
                value = value + sqlParameter[i].ToString() + "=" + sqlParameter[i].Value + ",";
            }
            return value;
        }
    }
}
